package MONITORING::DB::ORACLE::TABLESPACE_USAGE;

#################################################################
# $LastChangedDate: 2013-11-25 15:03:06 +0000 (Mon, 25 Nov 2013) $
# $LastChangedRevision: 22 $
# $LastChangedBy: aswod_83@gmx.net $
#################################################################
use strict;
use base qw(moniCA::DBH::Oracle);
use XML::Simple;
use Data::Dumper;
use Sys::Hostname;
use Params::Validate qw(:all);
use Getopt::Long;

#-------------------------------------------------------------------------------
sub Run {

#-------------------------------------------------------------------------------
# Description:
# Input Parms:
# Output Parms:
#-------------------------------------------------------------------------------
    my $self      = shift;
    my $cnf       = $self->GetCNF();
    my $logger    = $self->GetLogger();
    my $statefile = $self->TmpDir() . '/' . $self->Plug() . '.xml';
    my %newstat;

    #-------------------------------------
    #	validation of default parameters
    #-------------------------------------
    $self->_validCNFdefaults( $cnf->{defaults} );

    #-------------------------------------
    #	validation of identifier parameters
    #-------------------------------------
    $self->_validCNFidentifierAll();

    #-------------------------------------
    #	open database handle
    #-------------------------------------
    $self->ConnectDB( $self->{CNF}->{DBConnect} );

    #-------------------------------------
    #	collection of ALL tablespaces fill size
    #-------------------------------------
    $self->_getTablespace_status();

    #-------------------------------------
    #	pass through ALL tablespaces and compare fill size with treshold
    #-------------------------------------
    for ( sort keys %{ $self->{identifier} } ) {

        $self->{ID} = $_;
        my %tmpstat;
        my $oldstat = XMLin( $statefile, ForceArray => 1 )->{identifier}->{$_}
          if ( -s $statefile );
        my $failPct =
          $self->{IdCfg}->{$_}->{'failPct'} || $self->{DefCfg}->{'failPct'};
        my $warnPct =
          $self->{IdCfg}->{$_}->{'warnPct'} || $self->{DefCfg}->{'warnPct'};
        my $trigger =
          $self->{IdCfg}->{$_}->{'trigger'} || $self->{DefCfg}->{'trigger'};
        my $report =
          $self->{IdCfg}->{$_}->{'report'} || $self->{DefCfg}->{'report'};

        $tmpstat{values} = $self->{identifier}->{$_};

        $self->_validCNFrrd( $cnf->{rrd} ) && $self->_crReport()
          if ( $report =~ /on/i );

        if ( $self->{identifier}->{$_}->{usedPct} > $failPct ) {
            $tmpstat{failCnt} = ( $oldstat->{failCnt} + 1 ) || 1;
            $tmpstat{warnCnt} = 0;
            if ( $tmpstat{failCnt} == $trigger ) {
                $logger->error(
"$_ [usedPct:$self->{identifier}->{$_}->{usedPct}%|failPct:$failPct%]"
                );
            }
            else {
                $logger->info(
"$_ [usedPct:$self->{identifier}->{$_}->{usedPct}%|failPct:$failPct%]"
                );
            }
        }
        elsif ( $self->{identifier}->{$_}->{usedPct} > $warnPct ) {
            $tmpstat{warnCnt} = ( $oldstat->{warnCnt} + 1 ) || 1;
            $tmpstat{failCnt} = 0;
            if ( $tmpstat{warnCnt} == $trigger ) {
                $logger->warn(
"$_ [usedPct:$self->{identifier}->{$_}->{usedPct}%|warnPct:$warnPct%]"
                );
            }
            else {
                $logger->info(
"$_ [usedPct:$self->{identifier}->{$_}->{usedPct}%|warnPct:$warnPct%]"
                );
            }
        }
        else {
            $tmpstat{failCnt} = 0;
            $tmpstat{warnCnt} = 0;
            $logger->info("$_ [usedPct:$self->{identifier}->{$_}->{usedPct}%]");
        }
        $newstat{identifier}{$_} = \%tmpstat;
    }

    XMLout( \%newstat, outputfile => $statefile, NoSort => 0 );
}

#-------------------------------------------------------------------------------
sub _crReport {

#-------------------------------------------------------------------------------
# Description:
# Input Parms:
# Output Parms:
#-------------------------------------------------------------------------------

    my $self   = shift;
    my $logger = $self->GetLogger();

    eval {

        require RRDs;

        ( my $rrdident = $self->{ID} ) =~ s/\W/_/g;

        my $rrdfile =
            $self->{RRDCfg}->{rrdfilepath} . '/'
          . $self->Plug() . '_'
          . $rrdident . '.rrd'
          if ( -d $self->{RRDCfg}->{rrdfilepath} ) || die($!);
        my $rrdimg =
            $self->{RRDCfg}->{rrdimgpath} . '/'
          . $self->Plug() . '_'
          . $rrdident . '.png'
          if ( -d $self->{RRDCfg}->{rrdimgpath} ) || die($!);

        if ( !-f $rrdfile ) {
            my @rrdc;
            push( @rrdc, "$rrdfile" );
            push( @rrdc, "--step=$self->{RRDCfg}->{step}" );
            if ( ref( $self->{RRDCfg}->{DS} ) eq "ARRAY" ) {
                for ( @{ $self->{RRDCfg}->{DS} } ) { push( @rrdc, $_ ); }
            }
            else {
                push( @rrdc, $self->{RRDCfg}->{DS} );
            }
            for ( @{ $self->{RRDCfg}->{RRA} } ) { push( @rrdc, $_ ); }

            RRDs::create(@rrdc);

            $logger->warn( "RRDs::create returns " . RRDs::error() )
              if ( RRDs::error() );
        }

        my @identlist;
        if ( ref( $self->{RRDCfg}->{DS} ) eq "ARRAY" ) {
            for ( @{ $self->{RRDCfg}->{DS} } ) {
                my @tmplist = split( /:/, $_ );
                push( @identlist, $tmplist[1] );
            }
        }
        else {
            my @tmplist = split( /:/, $self->{RRDCfg}->{DS} );
            push( @identlist, $tmplist[1] );
        }
        my $dslist = join( ":", @identlist );

        if ( -f $rrdfile ) {
            my @rrdu;
            my @dsvalue;
            for (@identlist) {
                push( @dsvalue, $self->{identifier}->{ $self->{ID} }->{$_} );
            }
            my $dsvalue = join( ":", @dsvalue );

            push( @rrdu, "$rrdfile" );
            push( @rrdu, "--template=$dslist" );
            push( @rrdu, "N:$dsvalue" );

            RRDs::update(@rrdu);

            $logger->warn( "RRDs::update returns " . RRDs::error() )
              if ( RRDs::error() );

            my @rrdg;
            $rrdfile =~ s/\:\//\\\:\//g if ( $^O eq 'MSWin32' );

            push( @rrdg, "$rrdimg" );
            push( @rrdg, "--title=$self->{RRDCfg}->{'title'}: $self->{ID}" );
            push( @rrdg, "--start=$self->{RRDCfg}->{'start'}" );
            push( @rrdg, "--end=$self->{RRDCfg}->{'end'}" );
            push( @rrdg, "--vertical-label=$self->{RRDCfg}->{'label'}" );
            push( @rrdg, "--imgformat=$self->{RRDCfg}->{'imgformat'}" );
            push( @rrdg, "--watermark=$self->{RRDCfg}->{'watermark'}" );
            push( @rrdg, "--width=$self->{RRDCfg}->{'width'}" );
            push( @rrdg, "--height=$self->{RRDCfg}->{'height'}" );

            foreach ( keys %{ $self->{RRDCfg}->{DEF} } ) {
                push( @rrdg,
                    "DEF:$_=$rrdfile:$_:$self->{RRDCfg}->{DEF}->{$_}->{CF}" );
                push( @rrdg,
"$self->{RRDCfg}->{DEF}->{$_}->{TYPE}:$_#$self->{RRDCfg}->{DEF}->{$_}->{COLOR}:$_ $self->{RRDCfg}->{DEF}->{$_}->{CF}"
                );
            }

            RRDs::graph(@rrdg);

            $logger->warn( "RRDs::graph returns " . RRDs::error() )
              if ( RRDs::error() );
        }
        1;
    } or $logger->debug( "Report incomplete: " . $@ );

}

#-------------------------------------------------------------------------------
sub _getTablespace_status {

#-------------------------------------------------------------------------------
# Description:
# Input Parms:
# Output Parms:
#-------------------------------------------------------------------------------
    my $self = shift;
    my $stmt = q{
				SELECT
					d.tablespace_name "name",
					TRIM(TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99999990.900')) "totalMBytes",
					TRIM(TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999990.999')) "usedMBytes",
					TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 'FM990.00') "usedPct"
				FROM
					sys.dba_tablespaces d,
					(SELECT
						tablespace_name,
						sum(bytes) bytes
					FROM
						dba_data_files
					GROUP BY
						tablespace_name) a,
						(SELECT
							tablespace_name,
							sum(bytes) bytes
						FROM
							dba_free_space
						GROUP BY
							tablespace_name) f
						WHERE
							d.tablespace_name = a.tablespace_name(+) AND
							d.tablespace_name = f.tablespace_name(+) AND NOT
							(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
						UNION ALL
						SELECT
							d.tablespace_name "name",
							TRIM(TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99999990.900')) "totalMBytes",
							TRIM(TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99999990.999')) "usedMBytes",
							TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), 'FM990.00') "usedPct"
						FROM
							sys.dba_tablespaces d,
							(SELECT
								tablespace_name,
								sum(bytes) bytes
							FROM
								dba_temp_files
							GROUP BY
								tablespace_name) a,
								(SELECT
									tablespace_name,
									sum(bytes_cached) bytes
								FROM
									v$temp_extent_pool
								GROUP BY
									tablespace_name) t
								WHERE
									d.tablespace_name = a.tablespace_name(+) AND
									d.tablespace_name = t.tablespace_name(+) AND
									d.extent_management like 'LOCAL' AND
									d.contents like 'TEMPORARY'
				};
    my $sth = $self->{DBH}->prepare($stmt);
    $sth->execute();
    $self->{identifier} = $sth->fetchall_hashref('name');
    $sth->finish();
    return $self;
}

#-------------------------------------------------------------------------------
sub _validCNFrrd {

#-------------------------------------------------------------------------------
# Description:
# Input Parms:
# Output Parms:
#-------------------------------------------------------------------------------
    my $self = shift;

    Params::Validate::validation_options( allow_extra => 1 );
    $self->{RRDCfg} = validate(
        @_,
        {
            rrdfilepath => {
                type     => SCALAR,
                default  => $self->VarDir(),
                optional => 1,
                regex    => qr/^.+$/
            },
            rrdimgpath => {
                type     => SCALAR,
                default  => $self->VarDir() . "/images",
                optional => 1,
                regex    => qr/^.+$/
            },
            title  => { type => SCALAR },
            label  => { type => SCALAR },
            width  => { type => SCALAR, default => "400", regex => qr/\d\d\d/ },
            height => { type => SCALAR, default => "100", regex => qr/\d\d\d/ },
            start     => { type => SCALAR, default => "end-24hours" },
            end       => { type => SCALAR, default => "now" },
            imgformat => { type => SCALAR, regex   => qr/[PNG||GIF||PDF]/ },
            watermark =>
              { type => SCALAR, default => "powered by Andre Schmitt" },
            DS  => { type => ARRAYREF | SCALAR },
            RRA => { type => ARRAYREF | SCALAR }
        }
    );
    return $self;

}

#-------------------------------------------------------------------------------
sub _validCNFdefaults {

#-------------------------------------------------------------------------------
# Description:
# Input Parms:
# Output Parms:
#-------------------------------------------------------------------------------
    my $self = shift;

    Params::Validate::validation_options( allow_extra => 0 );
    $self->{DefCfg} = validate(
        @_,
        {
            warnPct => { type => SCALAR, regex => qr/^\d+$/i },
            failPct => { type => SCALAR, regex => qr/^\d+$/i },
            report  => { type => SCALAR, regex => qr/^(on||off)$/i },
            trigger => { type => SCALAR, regex => qr/^.+$/ }
        }
    );
    return $self;
}

#-------------------------------------------------------------------------------
sub _validCNFidentifier {

#-------------------------------------------------------------------------------
# Description:
# Input Parms:
# Output Parms:
#-------------------------------------------------------------------------------
    my $self = shift;

    Params::Validate::validation_options( allow_extra => 0 );
    $self->{IdCfg}->{ $self->{ID} } = validate(
        @_,
        {
            warnPct => { type => SCALAR, regex => qr/^\d+$/i, optional => 1 },
            failPct => { type => SCALAR, regex => qr/^\d+$/i, optional => 1 },
            report =>
              { type => SCALAR, regex => qr/^(on||off)$/i, optional => 1 },
            trigger => { type => SCALAR, regex => qr/^.+$/, optional => 1 }
        }
    );
    return $self;
}

#-------------------------------------------------------------------------------
sub _validCNFidentifierAll {

#-------------------------------------------------------------------------------
# Description:
# Input Parms:
# Output Parms:
#-------------------------------------------------------------------------------
    my $self = shift;
    my $cnf  = $self->GetCNF();

    foreach ( keys %{ $cnf->{identifier} } ) {
        $self->{ID} = $_;
        $self->_validCNFidentifier( $cnf->{identifier}->{$_} );
    }
}
1;

__END__
 
=pod
 
=head1 NAME
 
 TABLESPACE_USAGE.pm - oracle tablespace monitor 
 
=head1 SYNOPSIS
  
=head1 DESCRIPTION
 
=head1 PREREQUISITES
 
C<Log::Log4perl>
C<XML::Simple>
 
=head1 CONFIGURATION SAMPLE
 
 Modul      MONITORING::DB::ORACLE::TABLESPACE_USAGE
 Logger     moniCA
 LogLevel   DEBUG
 BriefDesc  Check tablespace
 DetailDesc This moniCA plug checks free storage capacity \
			Reporting method collects usedPct,usedBytes,totalBytes
			
 #DOC: 
 #DOC: 
 #DOC: 

 <defaults>
         report  = on
         failPct = 90
         warnPct = 80
         trigger = 2
 </defaults>

 #DOC: 
 #DOC: 
 #DOC: 

 <DBConnect>
         ConnectString dbi:Oracle:SID=XE;HOST=localhost;port=1521
         dbuser        SYSTEM
         dbpw          manager
 </DBConnect>

 #DOC: 
 #DOC: 
 #DOC: 

 <identifier "USERS">
         failPct = 2 
         warnPct = 1 
 </identifier>

 #DOC: 
 #DOC: 
 #DOC: 

 <rrd>
         #rrdimgpath  =
         #rrdfilepath =
         step         = 300
         DS           = DS:usedPct:GAUGE:1200:0:100
         DS           =	DS:totalMBytes:GAUGE:1200:0:1000000
         DS           = DS:usedMBytes:GAUGE:1200:0:1000000
         RRA          = RRA:AVERAGE:0.5:1:800
         RRA          = RRA:AVERAGE:0.5:6:800
         RRA          = RRA:AVERAGE:0.5:24:800
         RRA          = RRA:AVERAGE:0.5:288:800
         title        = Tablespace
         label        = TablespaceUsage in % 
         width        = 400
         height       = 100
         start        = end-24hours
         end          = now
         watermark    =	powered by SAM
         imgformat    =	PNG
         <DEF "usedPct">
                 CF    = AVERAGE
 			     TYPE  = LINE2
 			     COLOR = FFC0CB
         </DEF>
 </rrd>

=cut
